package data.courseselectiondata;

import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dataservice.courseselectiondataservice.CourseSelectionDataService;
import po.CourseClassification;
import po.CourseSelectionPO;
import po.Grade;
import po.Institute;
import po.LessonPO;
import po.StudentPO;
import po.TeacherPO;

public class CourseSelectionData extends UnicastRemoteObject implements
		CourseSelectionDataService {

	PreparedStatement ps = null;
	Connection ct = null;
	ResultSet rs = null;

	public CourseSelectionData() throws RemoteException {
	}

	@Override
	public void init() {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			ct = DriverManager.getConnection("jdbc:odbc:CourseSelectionSystem");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void finish() {
		try {
			if (ps != null) {
				ps.close();
			}
			if (ct != null) {
				ct.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings("finally")
	@Override
	public boolean insert(CourseSelectionPO po) {
		init();
		try {
			String sql = "select lessonId,term,studentId from CourseSelection where lessonId='"
					+ po.getLessonPO().getId()
					+ "' and term='"
					+ po.getLessonPO().getTerm()
					+ "' and studentId='"
					+ po.getStudentPO().getId() + "'";
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				if (rs != null) {
					rs.close();
				}
				finish();
				return false;
			}
			if (rs != null) {
				rs.close();
			}
			sql = "insert into CourseSelection values('"
					+ po.getLessonPO().getId() + "','"
					+ po.getLessonPO().getTerm() + "','"
					+ po.getStudentPO().getId() + "'," + po.getScore() + ",'"
					+ po.isQualified() + "','" + po.isValid() + "')";
			ps = ct.prepareStatement(sql);
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			finish();
			return true;
		}
	}

	@Override
	public boolean delete(String lessonId, String studentId, String term) {
		init();
		try {
			String sql = "delete from CourseSelection where lessonId='"
					+ lessonId + "' and term='" + term + "' and studentId='"
					+ studentId + "'";
			ps = ct.prepareStatement(sql);
			int i = ps.executeUpdate();
			finish();
			if (i == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean update(CourseSelectionPO po) {
		init();
		try {
			String sql = "update CourseSelection set score=" + po.getScore()
					+ ", isQualified='" + po.isQualified() + "', isValid='"
					+ po.isValid() + "' where lessonId='"
					+ po.getLessonPO().getId() + "' and term='"
					+ po.getLessonPO().getTerm() + "' and studentId='"
					+ po.getStudentPO().getId() + "'";
			ps = ct.prepareStatement(sql);
			int i = ps.executeUpdate();
			finish();
			if (i == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public CourseSelectionPO find(String lessonId, String term, String studentId) {
		init();
		CourseSelectionPO po = null;

		try {
			String sql = "select * from Teacher,Lesson,Student,CourseSelection where CourseSelection.lessonId='"
					+ lessonId
					+ "' and CourseSelection.term='"
					+ term
					+ "' and CourseSelection.studentId='"
					+ studentId
					+ "' and Lesson.id=CourseSelection.lessonId and Lesson.term=CourseSelection.term and Teacher.id=Lesson.teacherId and Student.id=CourseSelection.studentId";
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				TeacherPO teacherPO = new TeacherPO(rs.getString(1),
						rs.getString(2), rs.getString(3), Institute.toEnum(rs
								.getString(4)));

				LessonPO lessonPO = new LessonPO(rs.getString(5),
						rs.getString(6), rs.getInt(7), Institute.toEnum(rs
								.getString(8)), Grade.toEnum(rs.getString(9)),
						CourseClassification.toEnum(rs.getString(10)),
						rs.getString(11), teacherPO, rs.getString(13),
						rs.getString(14), rs.getString(15), rs.getInt(16),
						rs.getInt(17));

				StudentPO studentPO = new StudentPO(rs.getString(18),
						rs.getString(19), rs.getString(20), rs.getInt(21),
						Institute.toEnum(rs.getString(22)), rs.getInt(23));
				po = new CourseSelectionPO(lessonPO, studentPO,
						rs.getDouble(27), Boolean.valueOf(rs.getString(28)),
						Boolean.valueOf(rs.getString(29)));
			}
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			finish();
			return null;
		}
		finish();
		return po;
	}

	@Override
	public ArrayList<CourseSelectionPO> findByLesson(String lessonId,
			String term) {
		ArrayList<CourseSelectionPO> list = new ArrayList<CourseSelectionPO>();
		init();
		try {
			String sql = "select * from Teacher,Lesson,Student,CourseSelection where CourseSelection.lessonId='"
					+ lessonId
					+ "' and CourseSelection.term='"
					+ term
					+ "' and Lesson.id=CourseSelection.lessonId and Lesson.term=CourseSelection.term and Teacher.id=Lesson.teacherId and Student.id=CourseSelection.studentId order by CourseSelection.studentId";
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				TeacherPO teacherPO = new TeacherPO(rs.getString(1),
						rs.getString(2), rs.getString(3), Institute.toEnum(rs
								.getString(4)));

				LessonPO lessonPO = new LessonPO(rs.getString(5),
						rs.getString(6), rs.getInt(7), Institute.toEnum(rs
								.getString(8)), Grade.toEnum(rs.getString(9)),
						CourseClassification.toEnum(rs.getString(10)),
						rs.getString(11), teacherPO, rs.getString(13),
						rs.getString(14), rs.getString(15), rs.getInt(16),
						rs.getInt(17));

				StudentPO studentPO = new StudentPO(rs.getString(18),
						rs.getString(19), rs.getString(20), rs.getInt(21),
						Institute.toEnum(rs.getString(22)), rs.getInt(23));
				CourseSelectionPO po = new CourseSelectionPO(lessonPO,
						studentPO, rs.getDouble(27), Boolean.valueOf(rs
								.getString(28)), Boolean.valueOf(rs
								.getString(29)));
				list.add(po);

			}

			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			finish();
			return null;
		}
		finish();
		return list;
	}

	@Override
	public ArrayList<CourseSelectionPO> findByStudent(String studentId) {
		ArrayList<CourseSelectionPO> list = new ArrayList<CourseSelectionPO>();
		init();
		try {
			String sql = "select * from Teacher,Lesson,Student,CourseSelection where CourseSelection.studentId='"
					+ studentId
					+ "' and Lesson.id=CourseSelection.lessonId and Lesson.term=CourseSelection.term and Teacher.id=Lesson.teacherId and Student.id=CourseSelection.studentId order by CourseSelection.studentId";
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				TeacherPO teacherPO = new TeacherPO(rs.getString(1),
						rs.getString(2), rs.getString(3), Institute.toEnum(rs
								.getString(4)));

				LessonPO lessonPO = new LessonPO(rs.getString(5),
						rs.getString(6), rs.getInt(7), Institute.toEnum(rs
								.getString(8)), Grade.toEnum(rs.getString(9)),
						CourseClassification.toEnum(rs.getString(10)),
						rs.getString(11), teacherPO, rs.getString(13),
						rs.getString(14), rs.getString(15), rs.getInt(16),
						rs.getInt(17));

				StudentPO studentPO = new StudentPO(rs.getString(18),
						rs.getString(19), rs.getString(20), rs.getInt(21),
						Institute.toEnum(rs.getString(22)), rs.getInt(23));
				CourseSelectionPO po = new CourseSelectionPO(lessonPO,
						studentPO, rs.getDouble(27), Boolean.valueOf(rs
								.getString(28)), Boolean.valueOf(rs
								.getString(29)));
				list.add(po);
			}
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			return null;
		}
		finish();
		return list;
	}

	@Override
	public ArrayList<CourseSelectionPO> findByStudent(String studentId,
			String term) {
		ArrayList<CourseSelectionPO> list = new ArrayList<CourseSelectionPO>();
		init();
		try {
			String sql = "select * from Teacher,Lesson,Student,CourseSelection where CourseSelection.studentId='"
					+ studentId
					+ "' and CourseSelection.term='"
					+ term
					+ "' and Lesson.id=CourseSelection.lessonId and Lesson.term=CourseSelection.term and Teacher.id=Lesson.teacherId and Student.id=CourseSelection.studentId order by CourseSelection.studentId";
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				TeacherPO teacherPO = new TeacherPO(rs.getString(1),
						rs.getString(2), rs.getString(3), Institute.toEnum(rs
								.getString(4)));

				LessonPO lessonPO = new LessonPO(rs.getString(5),
						rs.getString(6), rs.getInt(7), Institute.toEnum(rs
								.getString(8)), Grade.toEnum(rs.getString(9)),
						CourseClassification.toEnum(rs.getString(10)),
						rs.getString(11), teacherPO, rs.getString(13),
						rs.getString(14), rs.getString(15), rs.getInt(16),
						rs.getInt(17));

				StudentPO studentPO = new StudentPO(rs.getString(18),
						rs.getString(19), rs.getString(20), rs.getInt(21),
						Institute.toEnum(rs.getString(22)), rs.getInt(23));
				CourseSelectionPO po = new CourseSelectionPO(lessonPO,
						studentPO, rs.getDouble(27), Boolean.valueOf(rs
								.getString(28)), Boolean.valueOf(rs
								.getString(29)));
				list.add(po);
			}
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			return null;
		}
		finish();
		return list;
	}

}
